package com.axdoc.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class TagsUserDoctorCheck20180126 {
	public static void main(String[] args) throws IOException {
		ApplicationContext ac = new FileSystemXmlApplicationContext("classpath:applicationContext.xml");
		JdbcTemplate jdbcTemplate=(JdbcTemplate) ac.getBean("jdbcTemplate");
		
		final Map<String,String> diseaseMap=new HashMap<String,String>();
		final Map<String,List<String>> map=new HashMap<String,List<String>>();
		jdbcTemplate.query("select disease_no,disease_name from disease", new RowMapper<Map<String,String>>() {

			@Override
			public Map<String, String> mapRow(ResultSet arg0, int arg1) throws SQLException {
				Map<String,String> map=new HashMap<String, String>();
				diseaseMap.put(arg0.getString("disease_no"), arg0.getString("disease_name"));
				return map;
			}
		});
		
		System.out.println(diseaseMap);
		jdbcTemplate.query("select name,disease_no from illness", new RowMapper<Map<String,String>>() {

			@Override
			public Map<String, String> mapRow(ResultSet arg0, int arg1) throws SQLException {
				List<String> strs=map.get(arg0.getString("name"));
				if(strs==null) {
					strs=new ArrayList<String>();
				}
				strs.add(diseaseMap.get(arg0.getString("disease_no")));
				map.put(arg0.getString("name"), strs);
				return null;
			}
		});
		
		System.out.println(map);
		
		
		File daichuli = new File("/Users/jiangboliu/work/files/userillness311.xls");
		InputStream dinputStream = new FileInputStream(daichuli);
		HSSFWorkbook dxssfWorkbook = new HSSFWorkbook(dinputStream);
		HSSFSheet dsheet1 = dxssfWorkbook.getSheetAt(0);
		
		
		Set<String> set=new HashSet<String>();
		
		
		
		
		

		int i=dsheet1.getLastRowNum();
		int j=dsheet1.getLastRowNum();
		int z=0;
		while (z<j) {
			z++;
			System.out.println(z);
			Row row=dsheet1.getRow(z);
			if(row==null||row.getPhysicalNumberOfCells()<5) {
				continue;
			}
			String a0=row.getCell(0).getStringCellValue();
			String a2=row.getCell(2).getStringCellValue();
			String a3=null;
			String a4=null;
			if(row.getCell(3)!=null) {
				a3=row.getCell(3).getStringCellValue();
			}
			
			if(row.getCell(4)!=null) {
				a4=row.getCell(4).getStringCellValue();
			}
			if("患者端无该病种".equals(a4)) {
				System.out.println(a0+"-------------"+a2+"------"+a3+"------"+a4);
				set.add(a0);
			}
			
			
		}
		
		for (String string : set) {
			i++;
			HSSFRow addRow = dsheet1.createRow(i);
			HSSFCell c=addRow.createCell(0);
			c.setCellValue(string);
		}
		
		
		 FileOutputStream out = new FileOutputStream("/Users/jiangboliu/work/files/userillness412.xls");
		 dxssfWorkbook.write(out);
	}
}
